-- Start of DDL Script for Package PEMEX2.PKG_SOLICITUD_AFECTADO
-- Generated 09/04/2012 23:28:56 from PEMEX2@XE

CREATE OR REPLACE 
PACKAGE pkg_solicitud_afectado
IS
   FUNCTION getgestoresturnado (pcve_trabajo_realizar NUMBER)
      RETURN sys_refcursor;

   FUNCTION getafectadosparticularporobra (pcve_obra NUMBER)
      RETURN sys_refcursor;

   PROCEDURE insertafectadofederal (
      pcve_afectado_federal   IN   VARCHAR2,
      pdescripcion            IN   VARCHAR2,
      porigen                 IN   VARCHAR2,
      pdestino                IN   VARCHAR2,
      pcve_trabajo_realizar   IN   VARCHAR2
   );

   PROCEDURE eliminarafectadofederal (pcve_afectado_federal IN VARCHAR2);

   PROCEDURE getafectadofederal (
      pcve_obra                     NUMBER,
      pcve_afectado_federal         NUMBER,
      resultado               OUT   sys_refcursor
   );

   FUNCTION getdatoafectadoPredio (pcve_predio VARCHAR2)
      RETURN sys_refcursor;

   FUNCTION getafectadosiarp (pcve_siarp VARCHAR2)
      RETURN sys_refcursor;

   FUNCTION getafectadoparticularSIARP (pcve_expediente_siarp VARCHAR2)
      RETURN sys_refcursor;

   PROCEDURE getafectadofederalcriterios (
      pobra            IN       VARCHAR2,
      pdesc_afectado   IN       VARCHAR2,
      resultado        OUT      sys_refcursor
   );
  PROCEDURE EliminarAfectadoParticular
     (
        pCVE_afectado IN Varchar2
     );

  PROCEDURE getafectadoparticularcriterios (
         pNoPredio   IN       VARCHAR2,
               pTipoErogacion in varchar2,
      pobra            IN       VARCHAR2,
      pNombreAfectado in varchar2,
      resultado        OUT      sys_refcursor
   );
  procedure  getAfectadosPorObra(pcve_solicitud_trabajos INT,resultado out sys_refcursor);
  PROCEDURE getafectadoparticular (
      pcve_afectado_particular                     NUMBER,
      resultado               OUT   sys_refcursor
   );
  PROCEDURE insertarafectadoparticular (
      pcve_predio                      IN       VARCHAR2,
      ppp_erogacion_verbal             IN       VARCHAR2,
      pobservaciones                   IN       VARCHAR2,
      parea_afectada                   IN       VARCHAR2,
      pcve_usuario                     IN       VARCHAR2,
      plonguitd_afectada               IN       VARCHAR2,
      pkilometro_inicial               IN       VARCHAR2,
      pkilometro_final                 IN       VARCHAR2,
      pcve_trabajo_realizar            IN       VARCHAR2,
      pfecha_envio_permiso_paso        IN       VARCHAR2,
      pfecha_obtencion_per_paso        IN       VARCHAR2,
      pobservacion_pp                  IN       VARCHAR2,
      pmonto_por_afectacion_aprobado   IN       VARCHAR2,
      pcve_solicitud_afectado          IN       VARCHAR2,
      pno_expediente                   IN       VARCHAR2,
      pfecha_comprometida_compromiso   IN       VARCHAR2,
      pfecha_cumplimiento_compromiso   IN       VARCHAR2
      --,pv_estatus_obra_devuelto         OUT      NUMBER
   );
END;                                                           -- Package spec
/


CREATE OR REPLACE 
PACKAGE BODY pkg_solicitud_afectado
IS
   FUNCTION getdatoafectadopredio (pcve_predio VARCHAR2)
      RETURN sys_refcursor
   IS
      resultado   sys_refcursor;
   BEGIN
      OPEN resultado FOR
         SELECT b.no_predio, a.cve_afectado,
                a.nombre || ' ' || a.apellido_p || ' ' || a.apellido_m
                                                                      nombre,
                a.sexo, a.rfc, a.clave_elector, b.cve_predio, b.denominado,
                b.superficie, c.descripcion tipo_acreditacion,
                d.descripcion localidad, e.descripcion municipio
           FROM cat_personas a,
                cat_predio b,
                cat_tipo_acreditacion c,
                cat_localidades d,
                cat_municipio e
          WHERE a.cve_afectado = b.cve_afectado
            AND b.cve_tipo_acreditacion = c.cve_tipo_acreditacion(+)
            AND b.cve_localidad = d.cve_localidad(+)
            AND d.cve_municipio = e.cve_municipio(+)
            AND b.no_predio = pcve_predio;

      RETURN resultado;
   END;

   FUNCTION getgestoresturnado (pcve_trabajo_realizar NUMBER)
      RETURN sys_refcursor
   IS
      resultado   sys_refcursor;
   BEGIN
      OPEN resultado FOR
         SELECT   b.cve_usuario, b.nombre
             FROM solicitud_turnar a, cat_usuario b
            WHERE a.cve_usuario = b.cve_usuario
              AND a.cve_trabajo_realizar = pcve_trabajo_realizar
         ORDER BY 2;

      RETURN resultado;
   END;

   FUNCTION getafectadosparticularporobra (pcve_obra NUMBER)
      RETURN sys_refcursor
   IS
      resultado   sys_refcursor;
   BEGIN
      OPEN resultado FOR
         SELECT ROWNUM num, b.no_predio,
                   ''
                || a.nombre
                || ' '
                || a.apellido_p
                || ' '
                || a.apellido_m
                || ', Predio: # '
                || b.no_predio
                || ' denominado '
                || b.denominado
                || ', Colindancias '
                || ' N:'
                || b.norte
                || ', S:'
                || b.sur
                || ', E:'
                || b.este
                || ', O:'
                || b.oeste
                || '. Escritura: '
                || b.no_escritura afectado,
                   '<strong>'
                || a.nombre
                || ' '
                || a.apellido_p
                || ' '
                || a.apellido_m
                || '</strong>, Predio: # '
                || b.no_predio
                || ' denominado '
                || b.denominado
                || ', Colindancias '
                || ' N:'
                || b.norte
                || ', S:'
                || b.sur
                || ', E:'
                || b.este
                || ', O:'
                || b.oeste
                || '. Escritura: '
                || b.no_escritura afectado_formato,
                   'Monto: '
                || c.monto_por_afectacion_aprobado
                || ' P.P: '
                || DECODE (c.pp_erogacion_verbal,
                           'E', 'Erogacion',
                           'V', 'Verbal'
                          )
                || ', Longuitud:'
                || c.longuitd_afectada
                || ', Km. inicial:'
                || c.kilometro_inicial
                || '  Km. final:'
                || c.kilometro_final afectacion,
                c.pp_erogacion_verbal,
                DECODE (c.fecha_obtencion_per_paso,
                        NULL, 'Pendiente',
                        DECODE (c.fecha_envio_permiso_paso,
                                NULL, 'PP. Obtenido',
                                'PP. Enviado'
                               )
                       ) estatus,
                TO_CHAR (c.fecha_envio_permiso_paso,
                         'DD/MM/YYYY'
                        ) fecha_envio_permiso_paso,
                TO_CHAR (c.fecha_obtencion_per_paso,
                         'DD/MM/YYYY'
                        ) fecha_obtencion_per_paso,
                DECODE
                     (c.fecha_obtencion_per_paso,
                      NULL, '0',
                      DECODE (c.fecha_envio_permiso_paso, NULL, '1', '2')
                     ) estatus_marcar_fila,
                TO_CHAR (c.fecha_registro, 'DD/MM/YYYY') f_registro, c.*,
                TO_CHAR (c.fecha_cumplimiento_compromiso,
                         'DD/MM/YYYY'
                        ) fecha_cumplimiento,
                TO_CHAR (c.fecha_comprometida_compromiso,
                         'DD/MM/YYYY'
                        ) fecha_comprometida,
                d.cve_solicitud,
                   iniciales (f.descripcion)
                || ':'
                || firstcap (   e.nombre
                             || ' '
                             || e.apellido_p
                             || ' '
                             || e.apellido_m
                            )
                || ':'
                || e.radio asignado,
                TRIM (TO_CHAR (c.monto_por_afectacion_aprobado,
                               '999,999,999,999.999'
                              )
                     ) monto
           FROM cat_personas a,
                cat_predio b,
                solicitud_afectado c,
                solicitud_trabajos_realizar d,
                cat_usuario e,
                cat_rol f
          WHERE a.cve_afectado = b.cve_afectado
            AND b.cve_predio = c.cve_predio
            AND c.cve_trabajo_realizar = d.cve_trabajo_realizar
            AND c.cve_usuario = e.cve_usuario(+)
            AND e.cve_rol = f.cve_rol(+)
            AND c.cve_trabajo_realizar = pcve_obra;

      RETURN resultado;
   END;

   FUNCTION getafectadoparticularsiarp (pcve_expediente_siarp VARCHAR2)
      RETURN sys_refcursor
   IS
      resultado   sys_refcursor;
   BEGIN
      OPEN resultado FOR
         SELECT b.no_predio,
                   a.nombre
                || ' '
                || a.apellido_p
                || ' '
                || a.apellido_m
                || ' Predio: # '
                || b.no_predio
                || ' denominado '
                || b.denominado
                || ', colindancias '
                || ' N:'
                || b.norte
                || ', S:'
                || b.sur
                || ', E:'
                || b.este
                || ', O:'
                || b.oeste
                || '. Escritura: '
                || b.no_escritura afectado,
                   'Monto: '
                || c.monto_por_afectacion_aprobado
                || ' P.P: '
                || DECODE (c.pp_erogacion_verbal,
                           'E', 'Erogacion',
                           'V', 'Verbal'
                          )
                || ', Longuitud:'
                || c.longuitd_afectada
                || ', Km. inicial:'
                || c.kilometro_inicial
                || '  Km. final:'
                || c.kilometro_final afectacion,
                c.pp_erogacion_verbal,
                DECODE (c.fecha_obtencion_per_paso,
                        NULL, 'Pendiente',
                        DECODE (c.fecha_envio_permiso_paso,
                                NULL, 'PP. Obtenido',
                                'PP. Enviado'
                               )
                       ) estatus,
                TO_CHAR (c.fecha_envio_permiso_paso,
                         'DD/MM/YYYY'
                        ) fecha_envio_permiso_paso,
                TO_CHAR (c.fecha_obtencion_per_paso,
                         'DD/MM/YYYY'
                        ) fecha_obtencion_per_paso,
                DECODE
                     (c.fecha_obtencion_per_paso,
                      NULL, '0',
                      DECODE (c.fecha_envio_permiso_paso, NULL, '1', '2')
                     ) estatus_marcar_fila,
                TO_CHAR (c.fecha_registro, 'DD/MM/YYYY') f_registro, c.*,
                TO_CHAR (c.fecha_cumplimiento_compromiso,
                         'DD/MM/YYYY'
                        ) fecha_cumplimiento,
                TO_CHAR (c.fecha_comprometida_compromiso,
                         'DD/MM/YYYY'
                        ) fecha_comprometida,
                d.cve_solicitud
           FROM cat_personas a,
                cat_predio b,
                solicitud_afectado c,
                solicitud_trabajos_realizar d
          WHERE a.cve_afectado = b.cve_afectado
            AND b.cve_predio = c.cve_predio
            AND c.cve_trabajo_realizar = d.cve_trabajo_realizar
            AND ROWNUM = 1
            AND homotext (c.no_expediente) = homotext (pcve_expediente_siarp);

      RETURN resultado;
   END;

   PROCEDURE insertafectadofederal (
      pcve_afectado_federal   IN   VARCHAR2,
      pdescripcion            IN   VARCHAR2,
      porigen                 IN   VARCHAR2,
      pdestino                IN   VARCHAR2,
      pcve_trabajo_realizar   IN   VARCHAR2
   )
   IS
      clave   NUMBER;
   BEGIN
      SELECT seq_afectado_federal.NEXTVAL
        INTO clave
        FROM DUAL;

      IF (pcve_afectado_federal IS NULL OR LENGTH (pcve_afectado_federal) = 0
         )
      THEN
         INSERT INTO solicitud_afectado_federal
                     (cve_afectado_federal, descripcion, origen, destino,
                      cve_trabajo_realizar
                     )
              VALUES (clave, pdescripcion, porigen, pdestino,
                      pcve_trabajo_realizar
                     );
      ELSE
         UPDATE solicitud_afectado_federal
            SET descripcion = pdescripcion,
                origen = porigen,
                destino = pdestino,
                cve_trabajo_realizar = pcve_trabajo_realizar
          WHERE cve_afectado_federal = pcve_afectado_federal;
      END IF;
   END;

   PROCEDURE eliminarafectadofederal (pcve_afectado_federal IN VARCHAR2)
   IS
   BEGIN
      DELETE FROM solicitud_afectado_federal
            WHERE cve_afectado_federal = pcve_afectado_federal;
   END;

   PROCEDURE getafectadofederal (
      pcve_obra                     NUMBER,
      pcve_afectado_federal         NUMBER,
      resultado               OUT   sys_refcursor
   )
   IS
   BEGIN
      OPEN resultado FOR
         SELECT   a.cve_afectado_federal, a.descripcion, a.origen, a.destino,
                  a.cve_trabajo_realizar,
                  TO_CHAR (a.fecha_registro, 'dd/mm/yyyy') fecha_registro,
                  b.desc_obra, s.numero_nota_turno
             FROM solicitud_afectado_federal a,
                  solicitud_trabajos_realizar b,
                  solicitud s
            WHERE a.cve_trabajo_realizar = b.cve_trabajo_realizar
              AND b.cve_solicitud = s.cve_solicitud
              AND b.cve_trabajo_realizar = pcve_obra
              AND cve_afectado_federal = pcve_afectado_federal
         ORDER BY a.fecha_registro DESC;
   END;

   PROCEDURE getafectadoparticular (
      pcve_afectado_particular                     NUMBER,
      resultado               OUT   sys_refcursor
   )
   IS
   BEGIN
      OPEN resultado FOR
         SELECT   a.cve_solicitud_afectado, a.pp_erogacion_verbal,
                  a.observaciones, a.area_afectada, a.cve_usuario,
                  a.longuitd_afectada, a.kilometro_inicial,
                  a.kilometro_final, TO_CHAR (a.fecha_envio_permiso_paso, 'dd/mm/yyyy') fecha_envio_permiso_paso,
                  TO_CHAR (a.fecha_obtencion_per_paso, 'dd/mm/yyyy') fecha_obtencion_per_paso, a.observacion_pp,
                  a.monto_por_afectacion_aprobado, TO_CHAR (a.fecha_registro, 'dd/mm/yyyy') fecha_registro,
                  a.no_expediente, TO_CHAR (a.fecha_cumplimiento_compromiso, 'dd/mm/yyyy') fecha_cumplimiento_compromiso,
                  TO_CHAR (a.fecha_comprometida_compromiso, 'dd/mm/yyyy') fecha_comprometida_compromiso, a.comentarios_carga,
                  a.cve_trabajo_realizar, a.cve_predio, a.cve_usuario
             FROM solicitud_afectado a
             WHERE a.cve_solicitud_afectado = pcve_afectado_particular

         ORDER BY a.fecha_registro DESC;
   END;

   FUNCTION getafectadosiarp (pcve_siarp VARCHAR2)
      RETURN sys_refcursor
   IS
      resultado   sys_refcursor;
   BEGIN
      OPEN resultado FOR
         SELECT b.no_predio,
                TRIM (a.nombre || ' ' || a.apellido_p || ' ' || a.apellido_m
                     ) afectado,
                DECODE (c.pp_erogacion_verbal,
                        'E', 'Erogacion',
                        'V', 'Verbal'
                       ) tipo_pp,
                c.pp_erogacion_verbal,
                DECODE (c.fecha_obtencion_per_paso,
                        NULL, 'Pendiente',
                        DECODE (c.fecha_envio_permiso_paso,
                                NULL, 'PP. Obtenido',
                                'PP. Enviado'
                               )
                       ) estatus,
                TO_CHAR (c.fecha_envio_permiso_paso,
                         'DD/MM/YYYY'
                        ) fecha_envio_permiso_paso,
                TO_CHAR (c.fecha_obtencion_per_paso,
                         'DD/MM/YYYY'
                        ) fecha_obtencion_per_paso,
                DECODE
                     (c.fecha_obtencion_per_paso,
                      NULL, '0',
                      DECODE (c.fecha_envio_permiso_paso, NULL, '1', '2')
                     ) estatus_marcar_fila,
                TO_CHAR (c.fecha_registro, 'DD/MM/YYYY') f_registro, c.*,
                b.*, d.cve_solicitud, v_obras.*
           FROM cat_personas a,
                cat_predio b,
                solicitud_afectado c,
                solicitud_trabajos_realizar d,
                v_obras
          WHERE a.cve_afectado = b.cve_afectado
            AND b.cve_predio = c.cve_predio
            AND c.cve_trabajo_realizar = d.cve_trabajo_realizar
            AND d.cve_trabajo_realizar = v_obras.cve_trabajo_realizar
            AND homotext (c.no_expediente) = homotext (pcve_siarp);

      RETURN resultado;
   END;

   PROCEDURE getafectadofederalcriterios (
      pobra            IN       VARCHAR2,
      pdesc_afectado   IN       VARCHAR2,
      resultado        OUT      sys_refcursor
   )
   IS
      sqltemp   VARCHAR (4000);
   BEGIN
      sqltemp :=
         '   SELECT  a.cve_afectado_federal, a.descripcion, a.origen,
                a.destino,a.cve_trabajo_realizar, to_char(a.fecha_registro,''dd/mm/yyyy'') fecha_registro,
                b.desc_obra, s.numero_nota_turno
        FROM    solicitud_afectado_federal a, solicitud_trabajos_realizar b, solicitud s
        WHERE
                a.cve_trabajo_realizar = b.cve_trabajo_realizar
                and b.cve_solicitud = s.cve_solicitud ';

      IF (pobra IS NOT NULL)
      THEN
         sqltemp :=
               sqltemp
            || ' AND homotext(b.desc_obra) LIKE ''%'
            || homotext (pobra)
            || '%''  ';
      END IF;

      IF (pdesc_afectado IS NOT NULL)
      THEN
         sqltemp :=
               sqltemp
            || ' AND homotext(a.descripcion) LIKE ''%'
            || homotext (pdesc_afectado)
            || '%''  ';
      END IF;

      sqltemp := sqltemp || ' order by a.FECHA_REGISTRO desc ';

      OPEN resultado FOR sqltemp;
   END;

   PROCEDURE insertarafectadoparticular (
      pcve_predio                      IN       VARCHAR2,
      ppp_erogacion_verbal             IN       VARCHAR2,
      pobservaciones                   IN       VARCHAR2,
      parea_afectada                   IN       VARCHAR2,
      pcve_usuario                     IN       VARCHAR2,
      plonguitd_afectada               IN       VARCHAR2,
      pkilometro_inicial               IN       VARCHAR2,
      pkilometro_final                 IN       VARCHAR2,
      pcve_trabajo_realizar            IN       VARCHAR2,
      pfecha_envio_permiso_paso        IN       VARCHAR2,
      pfecha_obtencion_per_paso        IN       VARCHAR2,
      pobservacion_pp                  IN       VARCHAR2,
      pmonto_por_afectacion_aprobado   IN       VARCHAR2,
      pcve_solicitud_afectado          IN       VARCHAR2,
      pno_expediente                   IN       VARCHAR2,
      pfecha_comprometida_compromiso   IN       VARCHAR2,
      pfecha_cumplimiento_compromiso   IN       VARCHAR2
      --,pv_estatus_obra_devuelto         OUT      NUMBER
   )
   IS
      clave                     NUMBER;
      v_envio_permiso           DATE   := NULL;
      v_obtencion_permisio      DATE   := NULL;
      v_compromiso              DATE   := NULL;
      v_cumplido                DATE   := NULL;
      v_cve_solicitud           NUMBER;
      v_estado_obra_solicitud   NUMBER := NULL;
      v_estado_obra_actual      NUMBER := NULL;
   BEGIN
      --pv_estatus_obra_devuelto := NULL;

      IF (LENGTH (TRIM (pfecha_envio_permiso_paso)) > 0)
      THEN
         v_envio_permiso := TO_DATE (pfecha_envio_permiso_paso, 'dd/mm/yyyy');
      END IF;

      IF (LENGTH (TRIM (pfecha_obtencion_per_paso)) > 0)
      THEN
         v_obtencion_permisio :=
                            TO_DATE (pfecha_obtencion_per_paso, 'dd/mm/yyyy');
      END IF;

      IF (LENGTH (TRIM (pfecha_comprometida_compromiso)) > 0)
      THEN
         v_compromiso :=
                       TO_DATE (pfecha_comprometida_compromiso, 'dd/mm/yyyy');
      END IF;

      IF (LENGTH (TRIM (pfecha_cumplimiento_compromiso)) > 0)
      THEN
         v_cumplido := TO_DATE (pfecha_cumplimiento_compromiso, 'dd/mm/yyyy');
      END IF;

      IF (   pcve_solicitud_afectado IS NULL
          OR LENGTH (TRIM (pcve_solicitud_afectado)) = 0
          OR pcve_solicitud_afectado = '0'
         )
      THEN
         SELECT seq_solicitud_afectado.NEXTVAL
           INTO clave
           FROM DUAL;
insert into paso (valor) valueS ('ENTRO A INSERTAR'); COMMIT;
         INSERT INTO solicitud_afectado
                     (cve_solicitud_afectado, cve_predio,
                      pp_erogacion_verbal, observaciones, area_afectada,
                      cve_usuario, longuitd_afectada, kilometro_inicial,
                      kilometro_final, cve_trabajo_realizar,
                      fecha_envio_permiso_paso, fecha_obtencion_per_paso,
                      observacion_pp, monto_por_afectacion_aprobado,
                      no_expediente, fecha_cumplimiento_compromiso,
                      fecha_comprometida_compromiso
                     )
              VALUES (clave, pcve_predio,
                      ppp_erogacion_verbal, pobservaciones, parea_afectada,
                      pcve_usuario, plonguitd_afectada, pkilometro_inicial,
                      pkilometro_final, pcve_trabajo_realizar,
                      v_envio_permiso, v_obtencion_permisio,
                      pobservacion_pp, pmonto_por_afectacion_aprobado,
                      pno_expediente, v_compromiso,
                      v_cumplido
                     );
      ELSE
      insert into paso (valor) valueS ('ENTRO A ACTUALIZAR cve_solicitud_afectado: '||pcve_solicitud_afectado); COMMIT;
         UPDATE solicitud_afectado
            SET cve_predio = pcve_predio,
                pp_erogacion_verbal = ppp_erogacion_verbal,
                observaciones = pobservaciones,
                area_afectada = parea_afectada,
                cve_usuario = pcve_usuario,
                longuitd_afectada = plonguitd_afectada,
                kilometro_inicial = pkilometro_inicial,
                kilometro_final = pkilometro_final,
                no_expediente = pno_expediente,
                cve_trabajo_realizar = pcve_trabajo_realizar,
                fecha_envio_permiso_paso = v_envio_permiso,
                fecha_obtencion_per_paso = v_obtencion_permisio,
                observacion_pp = pobservacion_pp,
                monto_por_afectacion_aprobado = pmonto_por_afectacion_aprobado,
                fecha_cumplimiento_compromiso = v_cumplido,
                fecha_comprometida_compromiso = v_compromiso,
                fecha_ultimo_cambio = SYSDATE
          WHERE cve_solicitud_afectado = pcve_solicitud_afectado;
      END IF;

      -- EN CASO QUE EL ESTADO CAMBIE SE NOTIFICARA POR E-MAIL
      SELECT x.cve_estatus
        INTO v_estado_obra_solicitud
        FROM solicitud_trabajos_realizar x
       WHERE x.cve_trabajo_realizar = pcve_trabajo_realizar;

      v_estado_obra_actual :=
                            pkg_trigger.getestatusobra (pcve_trabajo_realizar);

      UPDATE solicitud_trabajos_realizar
         SET cve_estatus = v_estado_obra_actual
       WHERE cve_trabajo_realizar = pcve_trabajo_realizar;

     /* IF (v_estado_obra_solicitud != v_estado_obra_actual)
      THEN
         pv_estatus_obra_devuelto := v_estado_obra_actual;
      END IF;*/

      SELECT cve_solicitud
        INTO v_cve_solicitud
        FROM solicitud_trabajos_realizar
       WHERE cve_trabajo_realizar = pcve_trabajo_realizar;

      UPDATE solicitud
         SET cve_estatus = pkg_trigger.getestatussolicitud (v_cve_solicitud)
       WHERE cve_solicitud = v_cve_solicitud;
   END;

   PROCEDURE eliminarafectadoparticular (pcve_afectado IN Varchar2)
   IS
      clave      NUMBER;
      cve_obra   NUMBER;
   BEGIN
      SELECT cve_trabajo_realizar
        INTO cve_obra
        FROM solicitud_afectado
       WHERE cve_solicitud_afectado = pcve_afectado;

      DELETE      afectado_envio_pp
            WHERE cve_solicitud_afectado = pcve_afectado;

      DELETE      solicitud_afectado
            WHERE cve_solicitud_afectado = pcve_afectado;

      UPDATE solicitud_trabajos_realizar
         SET cve_estatus = pkg_trigger.getestatusobra (cve_obra)
       WHERE cve_trabajo_realizar = cve_obra;
   END;

   PROCEDURE getafectadoparticularcriterios (
      pnopredio         IN       VARCHAR2,
      ptipoerogacion    IN       VARCHAR2,
      pobra             IN       VARCHAR2,
      pnombreafectado   IN       VARCHAR2,
      resultado         OUT      sys_refcursor
   )
   IS
      sqltemp   VARCHAR (4000);
   BEGIN
      sqltemp :=
         '  SELECT b.no_predio,
                TRIM (a.nombre || '' '' || a.apellido_p || '' '' || a.apellido_m
                     ) afectado,
                DECODE (c.pp_erogacion_verbal,
                        ''E'', ''Erogacion'',
                        ''V'', ''Verbal''
                       ) tipo_pp,
                c.pp_erogacion_verbal,
                DECODE (c.fecha_obtencion_per_paso,
                        NULL, ''Pendiente'',
                        DECODE (c.fecha_envio_permiso_paso,
                                NULL, ''PP. Obtenido'',
                                ''PP. Enviado''
                               )
                       ) estatus,
                TO_CHAR (c.fecha_envio_permiso_paso,
                         ''DD/MM/YYYY''
                        ) fecha_envio_permiso_paso,
                TO_CHAR (c.fecha_obtencion_per_paso,
                         ''DD/MM/YYYY''
                        ) fecha_obtencion_per_paso,
                DECODE
                     (c.fecha_obtencion_per_paso,
                      NULL, ''0'',
                      DECODE (c.fecha_envio_permiso_paso, NULL, ''1'', ''2'')
                     ) estatus_marcar_fila,
                TO_CHAR (c.fecha_registro, ''DD/MM/YYYY'') f_registro, d.cve_solicitud,c.area_afectada, c.longuitd_afectada,c.kilometro_inicial,c.kilometro_final,
                trim(to_char(to_char(c.monto_por_afectacion_aprobado,''999,999,999,999.99'')))monto,
                v_obras.desc_obra,v_obras.numero_nota_turno
                ,v_obras.cve_trabajo_realizar, c.cve_solicitud_afectado,c.cve_predio
                ,c.no_expediente
                ,u.nombre ||'' ''||u.apellido_p||'' ''||u.apellido_m asignado
           FROM cat_personas a,
                cat_predio b,
                solicitud_afectado c,
                solicitud_trabajos_realizar d,
                v_obras,
                cat_usuario u
          WHERE a.cve_afectado = b.cve_afectado
            AND b.cve_predio = c.cve_predio
            AND c.cve_trabajo_realizar = d.cve_trabajo_realizar
            AND d.cve_trabajo_realizar = v_obras.cve_trabajo_realizar
            and c.cve_usuario = u.cve_usuario(+) ';

      IF (pobra IS NOT NULL)
      THEN
         sqltemp :=
               sqltemp
            || ' AND homotext(v_obras.desc_obra) LIKE ''%'
            || homotext (pobra)
            || '%''  ';
      END IF;

      IF (ptipoerogacion IS NOT NULL)
      THEN
         sqltemp :=
               sqltemp
            || ' AND homotext(c.pp_erogacion_verbal) LIKE ''%'
            || homotext (ptipoerogacion)
            || '%''  ';
      END IF;

      IF (pnopredio IS NOT NULL)
      THEN
         sqltemp :=
               sqltemp
            || ' AND homotext(b.no_predio) LIKE ''%'
            || homotext (pnopredio)
            || '%''  ';
      END IF;

      IF (pnombreafectado IS NOT NULL)
      THEN
         sqltemp :=
               sqltemp
            || ' and HOMOTEXT(a.nombre||'' ''||a.apellido_p||'' ''||a.apellido_m) like HOMOTEXT (REPLACE (''%'
            || pnombreafectado
            || '%'', '' '', ''%''))';
      END IF;

      sqltemp := sqltemp || ' order by c.FECHA_REGISTRO desc ';

      OPEN resultado FOR sqltemp;
   END;

procedure  getAfectadosPorObra(pcve_solicitud_trabajos INT,resultado out sys_refcursor)
    IS
    condiciones varchar2(1000);
    cadena varchar2(4000);
    BEGIN
        OPEN resultado FOR
            SELECT * FROM V_AFECTADOS WHERE CVE_TRABAJO_REALIZAR = pcve_solicitud_trabajos ORDER BY
            NOMBRE||' '||APELLIDO_P||' '||APELLIDO_M;

    END;
END;
/


-- End of DDL Script for Package PEMEX2.PKG_SOLICITUD_AFECTADO
